#! /usr/bin/env python
# -*- coding:utf-8 -*-
import os,sys
import platform
import pymysql
import logging
logging.basicConfig(level=logging.DEBUG, #日志级别为DEBUG
                    format="%(asctime)s %(pathname)s %(levelname)s %(lineno)d %(message)s", #日志格式设置
                    datefmt = '%Y-%m-%d  %H:%M:%S %a',    #日期格式设置
                    filename = os.getcwd()+'/common/logging.log')  #日志输入文件地址

"""
实现连接数据库、查询、插入删除等数据库操作
"""
def db_con(filename='mysql_conn.cnf'):
    # 从txt文件中读取数据库连接信息
    db = None
    curpath = os.path.abspath(os.path.dirname(__file__))
    if platform.system().lower() == 'windows':
        rootpath = curpath + '\common\\'
    else:
        rootpath = curpath + '/common/'
    with open(rootpath+filename, 'r', encoding='utf-8') as f:
        dic = []
        for line in f.readlines():
            line = line.strip('\n')
            b = line.split(':')
            dic.append(b)
    dic = dict(dic)
    dic["port"]=int(dic["port"])
    if 'port' in dic :
        dic["port"] = int(dic['port'])
    else:
        dic["port"] = 3306
    if 'charset' not in dic :
        dic["charset"] = 'utf8'
#     print(dic)
    # 建立数据库连接
    try:
        db = pymysql.connect(**dic)
    except Exception as e:
        print(u"连接失败",e)
    return db

'''
使用类形式，不返回连接，类方法中完成sql 调用 ，可以使用with调用，自送销毁连接
'''
class Session(object):
    # 初始化对象，从配置文件读取信息，产生一个mysql连接
    def __init__(self,filename='mysql_conn.cnf'):
        try:
            self.filename = ''
            curpath = os.path.abspath(os.path.dirname(__file__))
            rootpath=curpath+os.sep
            self.filename = rootpath+"common/"+filename
            with open(self.filename, 'r', encoding='utf-8') as f:
                dic = []
                for line in f.readlines():
                    line = line.strip('\n')
                    b = line.split(':')
                    dic.append(b)
                dic = dict(dic)
                self._ip = dic['host']
                self._user = dic['user']
                self._passwd =  dic['password']
                self._db = dic['database']
                if 'port' in dic :
                    self._port = int(dic['port'])
                else:
                    self._port = 3306
                if 'charset' in dic :
                    self._charset = dic['charset']
                else:
                    self._charset = 'utf8'
                    
                self._conn = pymysql.connect(
                    host=self._ip,
                    user=self._user,
                    password=self._passwd,
                    database=self._db,
                    charset=self._charset,
                    port=self._port,
                    autocommit=True
            )
        except Exception as err:
            format_err = f"ERROR - {self.filename} session init failed: {err}" + "\n"
            raise Exception(format_err)
    '''
        手动设置 数据库配置参数
    '''
    def setdb(self, ip, user, passwd, db, port=3306, charset='utf8'):
        self._ip = ip
        self._user = user
        self._passwd = passwd
        self._db = db
        self._port = port
        self._charset = charset
        self._conn = None
        self._cursor = None
        try:
            self._conn = pymysql.connect(
                host=self._ip,
                user=self._user,
                password=self._passwd,
                database=self._db,
                charset=self._charset,
                port=self._port,
                autocommit=True
            )
        except Exception as err:
            format_err = f"ERROR - {self._ip} session init failed: {err}" + "\n"
            raise Exception(format_err)
    # 调用with方法的入口
    def __enter__(self):
        return self
    #调用with方法结束时启动
    def __exit__(self, exc_type, exc_val, exc_tb):
        self._conn.close()
    def get_session(self):
        return self._conn
    #查询函数
    def query(self, sql,value_list=None):
        """
        :param sql: 查询语句
        :return: result-查询结果;
        """
        cursor = self._conn.cursor(pymysql.cursors.DictCursor)
        try:
            result = []
            rows = cursor.execute(sql,value_list)
            if rows > 0:
                sql_result = cursor.fetchall()
                # result = [list(i) for i in sql_result]
                result = sql_result
            cursor.close()
            return result
        except Exception as err:
            format_err = f"ERROR -  query failed: {err} - {sql,value_list}" + "\n"
            raise Exception(format_err)
        finally:
            cursor.close()
    #单条dml语句
    def change(self, sql,value_list=None):
        """
        :param sql: dml语句
        :return : rows-改动的记录条数
        """
        cursor = self._conn.cursor()
        try:
            rows = cursor.execute(sql,value_list)
            return rows
        except Exception as err:
            format_err = f"ERROR - change failed: {err} - {sql}" + "\n"
            raise Exception(format_err)
        finally:
            cursor.close()
    #多条dml语句
    def change_many(self, sql, value_list=None):
        """
        :param sql: 修改语句
        :param value_list: 参数值列表
        :return:rows-改动的记录条数
        """
        cursor = self._conn.cursor()
        try:
            rows = cursor.executemany(sql, value_list)
            return rows
        except Exception as err:
            format_err = f"ERROR - change_many failed: {err} - {sql}" + "\n"
            raise Exception(format_err)
        finally:
            cursor.close()

if __name__ == "__main__":

    with Session(  ) as session:
        sql_insert_many = 'select 1 '        

        row = session.query(sql_insert_many)
        # print(row)


    
    
